package com.dfbz.springboot_mybatisplus;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.dfbz.springboot_mybatisplus.entity.HospitalMeta;
import com.dfbz.springboot_mybatisplus.entity.User;
import com.dfbz.springboot_mybatisplus.mapper.HospitalMapper;
import com.dfbz.springboot_mybatisplus.mapper.UserMapper;
import org.junit.Test;
import org.junit.platform.commons.util.StringUtils;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.time.LocalDateTime;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

@SpringBootTest
@RunWith(SpringRunner.class)
public class Demo2 {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void inster() {
        User user = new User();
        user.setAge(25);
        user.setName("向后");
        user.setEmail("1611097988@qq.com");
        user.setCreateTime(LocalDateTime.now());
        int insert = userMapper.insert(user);
        System.out.println(insert);
    }

    @Test
    public void selectByWrapper3() {
        /**
         * 名字姓王或者年龄大于等于25 按年龄降序排序  年龄相同 按id升序排序
         */
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.likeRight("name", "王").or().ge("age", "25").orderByDesc("age").orderByAsc("id");
        List<User> users = userMapper.selectList(userQueryWrapper);
        users.forEach(System.out::println);
    }

    @Test
    public void selectByWrapper4() {
        /**
         * 创建日期为2019年2月14日 并且直属上级名字为王姓
         * date_formate(create_time,%Y_%m_%d) and manage_id in (select id from user where name like "王%")
         */
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.apply("date_format(create_time,'%Y_%m_%d')={0}", "2019-02-14")
                .inSql("manager_id", "select id from user where name like '王%'");
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }

    /**
     * 名字为姓王 并且(年龄小于40或者邮箱不为空)
     */
    @Test
    public void selectByWrapper5() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.likeRight("name", "王").and(wq -> wq.lt("age", 40).or().isNotNull("email"));
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    /**
     * 名字为王姓或者(年龄小于40并且年龄大于20并且邮箱不为空)
     */
    @Test
    public void selectByWrapper6() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.likeRight("name", "王").or(wq -> wq.lt("age", 40).gt("age", 20).isNotNull(
                "email"
        ));
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    /**
     * (年龄小于40或者邮箱不为空) 并且名字为王姓
     * (age<40 or email is not null) and name like '王%'
     * or的优先级小于and
     * 不加括号email is not null) and name like '王%'  再执行age<40
     */
    @Test
    public void selectByWrapper7() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.nested(wq -> wq.lt("age", 40).isNotNull("email")).likeRight("name", "王");
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    /**
     * 年龄在30 31 32 35
     */
    @Test
    public void selectByWrapper8() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("age", Arrays.asList(30, 32, 35));
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    /**
     * 只返回一条  limit 1
     */
    @Test
    public void selectByWrapper88() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("age", Arrays.asList(30, 32, 35)).last("limit 1");
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    /***
     * select不查询出全部列的方法
     * 名字中包含雨 并且年龄小于四十的
     */
    @Test
    public void selectByWrapper9() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("age", "name").like("name", "雨").lt("age", "40");
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    /**
     * 排除上级  排除创建时间
     */
    @Test
    public void selectByWrapper10() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        //  四大范式之一 predicate
        queryWrapper.select(User.class, info -> !info.getColumn().equals("create_time") &&
                !info.getColumn().equals("manager_id")).like("name", "雨").lt("age", "40");
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    /**
     * condition  参数有效性判断  姓名邮箱模糊查询  不为空 才到like
     */
    @Test
    public void selectByWrapper11() {
        String name = "";
        String email = "163";
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        //  四大范式之一 predicate
        queryWrapper.like(StringUtils.isNotBlank(name), "name", "王");
        queryWrapper.like(StringUtils.isNotBlank(email), "email", email);
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    /**
     * 实体作为构造器
     */
    @Test
    public void selectByWrapper12() {
        User user = new User();
        user.setName("刘");
        user.setAge(23);
        QueryWrapper<User> queryWrapper = new QueryWrapper<User>(user);
        //  四大范式之一 predicate
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    /**
     * lambda表达式   防止误写
     */
    @Test
    public void selectByWrapper13() {
        LambdaQueryWrapper<User> lambdaQueryWrapper = new QueryWrapper<User>().lambda();
        //  function   user
        lambdaQueryWrapper.like(User::getManagerId, "553");
        List<User> users = userMapper.selectList(lambdaQueryWrapper);
        users.forEach(System.out::println);
    }

    @Test
    public void selectByWrapper14() {
        LambdaQueryWrapper<User> lambdaQueryWrapper = new QueryWrapper<User>().lambda();
        //  wq是一个引用
        lambdaQueryWrapper.like(User::getName, "王").and(wq -> wq.lt(User::getAge, "30").or().isNotNull(User::getEmail));
        List<User> users = userMapper.selectList(lambdaQueryWrapper);
        users.forEach(System.out::println);
    }

    /**
     * 分页
     */
    @Test
    public void selectByWrapperPage() {
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.lt("age", 26);
        Page<User> page = new Page<User>(1, 2);
        IPage<User> userPage = userMapper.selectPage(page, userQueryWrapper);
        System.out.println("总页数"+userPage.getPages());
        System.out.println("总记录数"+userPage.getTotal());
        List<User> records = userPage.getRecords();
        records.forEach(System.out::println);

    }

    /**
     * 分页 记录返回的是map
     */
    @Test
    public void selectByWrapperPageMap() {
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.lt("age", 26);
        Page<User> page = new Page<User>(1, 2);
        IPage<Map<String, Object>> mapIPage = userMapper.selectMapsPage(page, userQueryWrapper);
        System.out.println("总页数"+mapIPage.getPages());
        System.out.println("总记录数"+mapIPage.getTotal());
        List<Map<String, Object>> records = mapIPage.getRecords();
        records.forEach(System.out::println);

    }


}
